import time
import traceback
from datetime import date

from backend.app.app.api.api_Hu.crud.pro.tb_userpro_input import create_tree
from backend.app.app.db.session_public import session_pub
import backend.app.app.models.db_owi_public_models as  md_pub

#   修改数据
def modify_page(userid,projid,page_data,remark):
    try:
        session_pub.query(md_pub.PageDvpuintproj1) \
            .filter(md_pub.PageDvpuintproj1.user_id == userid,
                    md_pub.PageDvpuintproj1.proj_id == projid).update({
            md_pub.PageDvpuintproj1.user_id : userid,
            md_pub.PageDvpuintproj1.proj_id: projid,
            md_pub.PageDvpuintproj1.data: page_data,
            md_pub.PageDvpuintproj1.remark: remark
        })
        session_pub.commit()
        print('更新数据成功')
        return True
    except:
        print('更新数据失败')
        return False

#   新增数据
def add_page(userid,projid,data,remark):
    try:
        session_pub.add(md_pub.PageDvpuintproj1(user_id=userid,
                                               proj_id=projid,
                                               data=data,
                                               remark=remark))
        session_pub.commit()
        print('储存成功')
        return True
    except:
        print('储存失败')
        session_pub.rollback()
        return False


#   读取页面id
def get_up_id():
    try:
        result = session_pub.query(md_pub.PageDvpuintproj1.user_id,
                                   md_pub.PageDvpuintproj1.proj_id
                                   ).all()
        return result
    except:
        print('查找失败')
        session_pub.rollback()
        return False

#   读取页面数据
def get_page_data(user_id,proj_id):
    try:
        result = session_pub.query(md_pub.PageDvpuintproj1.user_id,
                                   md_pub.PageDvpuintproj1.proj_id,
                                   md_pub.PageDvpuintproj1.data,
                                   md_pub.PageDvpuintproj1.remark
                                   ).filter(md_pub.PageDvpuintproj1.user_id == user_id,
                                            md_pub.PageDvpuintproj1.proj_id == proj_id).all()
        return result
    except:
        print('查找失败')
        session_pub.rollback()
        return False

#   删除数据
def clear_data():
    try:
        session_pub.query(md_pub.TbYearDvpunitOilstimPlanprojMessage).delete()
        session_pub.commit()
        print('删除数据成功')
        return True
    except:
        print('删除数据失败')
        return False

#    读 名称、措施类、起始年、结束年
def get_name_year():
    try:
        result = session_pub.query(md_pub.TbYearDvpunitOilstimPlanprojMessage.DvpUnit_Name,
                                   md_pub.TbYearDvpunitOilstimPlanprojForecast.StimType_Name,
                                   md_pub.TbYearDvpunitOilstimPlanprojMessage.StimPlanProj_StartYear,
                                   md_pub.TbYearDvpunitOilstimPlanprojMessage.StimPlanProj_EndYear
                                   ).all()
        return result
    except:
        print('查找失败')
        session_pub.rollback()
        return False

#   新增数据
def add_data(data):
    DvpUnit_OilStim_PlanProj_ID = str(time.time()).replace(".", "")
    try:
        create_time=date.today()
        session_pub.add(md_pub.TbYearDvpunitOilstimPlanprojMessage(
            DvpUnit_OilStim_PlanProj_ID=DvpUnit_OilStim_PlanProj_ID,
            DvpUnit_Name=data["DvpUnit_Name"],
            DvpUnit_OilStim_PlanProj_Name=data["DvpUnit_OilStim_PlanProj_Name"],
            StimPlanProj_StartYear=data["StimPlanProj_StartYear"],
            StimPlanProj_EndYear=data["StimPlanProj_EndYear"],
            StimPlanProj_Outline=data["StimPlanProj_Outline"],
            Create_User_Name=data['userName'],
            Create_Date=create_time,
            Update_Date=create_time
        ))
        session_pub.commit()
        return {
                'DvpUnit_OilStim_PlanProj_ID': DvpUnit_OilStim_PlanProj_ID,
                'DvpUnit_Name': data["DvpUnit_Name"],
                'DvpUnit_OilStim_PlanProj_Name': data["DvpUnit_OilStim_PlanProj_Name"],
                'result': 'save success'
            }
    except:
        print(traceback.print_exc())
        session_pub.rollback()
        return {
                'DvpUnit_OilStim_PlanProj_ID': DvpUnit_OilStim_PlanProj_ID,
                'DvpUnit_Name': data["DvpUnit_Name"],
                'DvpUnit_OilStim_PlanProj_Name': data["DvpUnit_OilStim_PlanProj_Name"],
                'result': 'save false'
            }

#   删除数据
def delete_data(data):
    try:
        session_pub.query(md_pub.TbYearDvpunitOilstimPlanprojMessage) \
            .filter(md_pub.TbYearDvpunitOilstimPlanprojMessage.DvpUnit_OilStim_PlanProj_ID == data["DvpUnit_OilStim_PlanProj_ID"],
            md_pub.TbYearDvpunitOilstimPlanprojMessage.DvpUnit_Name == data["DvpUnit_Name"],
                    md_pub.TbYearDvpunitOilstimPlanprojMessage.DvpUnit_OilStim_PlanProj_Name == data["DvpUnit_OilStim_PlanProj_Name"]).delete()
        session_pub.commit()
        return {
                'DvpUnit_OilStim_PlanProj_ID': data["DvpUnit_OilStim_PlanProj_ID"],
                'DvpUnit_Name': data["DvpUnit_Name"],
                'DvpUnit_OilStim_PlanProj_Name': data["DvpUnit_OilStim_PlanProj_Name"],
                'result': 'delete success'
            }
    except:
        print(traceback.print_exc())
        session_pub.rollback()
        return {
                'DvpUnit_OilStim_PlanProj_ID': data["DvpUnit_OilStim_PlanProj_ID"],
                'DvpUnit_Name': data["DvpUnit_Name"],
                'DvpUnit_OilStim_PlanProj_Name': data["DvpUnit_OilStim_PlanProj_Name"],
                'result': 'delete false'
            }

#   修改数据
def modify_data(data):
    try:
        delete_time = date.today()
        session_pub.query(md_pub.TbYearDvpunitOilstimPlanprojMessage).filter(
            md_pub.TbYearDvpunitOilstimPlanprojMessage.DvpUnit_OilStim_PlanProj_ID == data["DvpUnit_OilStim_PlanProj_ID"],
            md_pub.TbYearDvpunitOilstimPlanprojMessage.DvpUnit_Name == data["DvpUnit_Name"],
            md_pub.TbYearDvpunitOilstimPlanprojMessage.DvpUnit_OilStim_PlanProj_Name == data["DvpUnit_OilStim_PlanProj_Name"]
        ).update({
            md_pub.TbYearDvpunitOilstimPlanprojMessage.DvpUnit_Name : data["DvpUnit_Name"],
            md_pub.TbYearDvpunitOilstimPlanprojMessage.DvpUnit_OilStim_PlanProj_Name: data["DvpUnit_OilStim_PlanProj_Name"],
            md_pub.TbYearDvpunitOilstimPlanprojMessage.StimPlanProj_StartYear: data["StimPlanProj_StartYear"],
            md_pub.TbYearDvpunitOilstimPlanprojMessage.StimPlanProj_EndYear: data["StimPlanProj_EndYear"],
            md_pub.TbYearDvpunitOilstimPlanprojMessage.StimPlanProj_Outline: data["StimPlanProj_Outline"],
            md_pub.TbYearDvpunitOilstimPlanprojMessage.Update_Date: delete_time,
        })
        session_pub.commit()
        return {
                'DvpUnit_OilStim_PlanProj_ID': data["DvpUnit_OilStim_PlanProj_ID"],
                'DvpUnit_Name': data["DvpUnit_Name"],
                'DvpUnit_OilStim_PlanProj_Name': data["DvpUnit_OilStim_PlanProj_Name"],
                'result': 'modify success'
            }
    except:
        print(traceback.print_exc())
        session_pub.rollback()
        return {
                'DvpUnit_OilStim_PlanProj_ID': data["DvpUnit_OilStim_PlanProj_ID"],
                'DvpUnit_Name': data["DvpUnit_Name"],
                'DvpUnit_OilStim_PlanProj_Name': data["DvpUnit_OilStim_PlanProj_Name"],
                'result': 'modify false'
            }

#    读 名称
def get_name():
    try:
        d_infs = session_pub.query(
            md_pub.TbDvpunitbaisc.DvpUnit_ID,
            md_pub.TbDvpunitbaisc.DvpUnit_Name,
            md_pub.TbDvpunitbaisc.Belong_DvpUnit_Name,
            md_pub.TbDvpunitbaisc.DvpUnit_Rank,
        ).all()
        data = [dict(zip(result.keys(), result)) for result in d_infs]
        plan_infs = session_pub.query(md_pub.TbYearDvpunitOilstimPlanprojMessage.DvpUnit_OilStim_PlanProj_Name).all()
        return data, plan_infs
    except:
        print('查找失败')
        session_pub.rollback()
        return False

#根据单元名称、方案名称获取信息
def query_data(dvpUnit_Name:list,PlanProj_Name:list):
    try:
        data = session_pub.query(md_pub.TbYearDvpunitOilstimPlanprojMessage.DvpUnit_OilStim_PlanProj_ID,
                                        md_pub.TbYearDvpunitOilstimPlanprojMessage.DvpUnit_Name,
                                        md_pub.TbYearDvpunitOilstimPlanprojMessage.DvpUnit_OilStim_PlanProj_Name,
                                        md_pub.TbYearDvpunitOilstimPlanprojMessage.StimPlanProj_StartYear,
                                        md_pub.TbYearDvpunitOilstimPlanprojMessage.StimPlanProj_EndYear,
                                        md_pub.TbYearDvpunitOilstimPlanprojMessage.StimPlanProj_Outline
                                        )\
            .filter(md_pub.TbYearDvpunitOilstimPlanprojMessage.DvpUnit_Name.in_(dvpUnit_Name),
                    md_pub.TbYearDvpunitOilstimPlanprojMessage.DvpUnit_OilStim_PlanProj_Name.in_(PlanProj_Name)
                    )\
            .order_by(md_pub.TbYearDvpunitOilstimPlanprojMessage.DvpUnit_Name,
                      md_pub.TbYearDvpunitOilstimPlanprojMessage.StimPlanProj_StartYear,
                      md_pub.TbYearDvpunitOilstimPlanprojMessage.StimPlanProj_EndYear,
                      md_pub.TbYearDvpunitOilstimPlanprojMessage.DvpUnit_OilStim_PlanProj_Name).all()

        return data
    except:
        print('输入数据查找数据失败')
        session_pub.rollback()
        return False

def page_recovery_read(user_id:str,proj_id:str):
    try:
        result=session_pub.query(
                    md_pub.TbPageRecovery.UserID,
                    md_pub.TbPageRecovery.ProJID,
                    md_pub.TbPageRecovery.Tab1,
                    md_pub.TbPageRecovery.Tab2,
                    md_pub.TbPageRecovery.Tab3,
                    ).filter(
                        md_pub.TbPageRecovery.UserID==user_id,
                        md_pub.TbPageRecovery.ProJID==proj_id,
                        ).first()
        if result:
            result= dict(zip(result.keys(), result))
        return result
    except Exception as e:
        return e


def page_recovery_save(page_recovery_list):
    try:
        session_pub.query(md_pub.TbPageRecovery).delete()
        for page_recovery in page_recovery_list:
            if page_recovery.userid and page_recovery.projid:
                session_pub.add(
                            md_pub.TbPageRecovery(
                                UserID=page_recovery.userid,
                                ProJID=page_recovery.projid,
                                Tab1=page_recovery.tab1,
                                Tab2=page_recovery.tab2,
                                Tab3=page_recovery.tab3
                                )
                )
        session_pub.commit()
        return True
    except Exception as e:
        session_pub.rollback()
        print(e)
        return e

def get_stim_type():
    try:
        stim_type = session_pub.query(
            md_pub.TbOilwellstimType.StimType_Name,
            md_pub.TbOilwellstimType.Belong_StimType_Name,
            md_pub.TbOilwellstimType.StimType_Rank,
            md_pub.TbOilwellstimType.StimType_ID
        ).all()
        stim_type = [dict(zip(type.keys(), type)) for type in stim_type]
        # 处理措施数据，生成树形结构
        stim_type_tree = list()
        for type in stim_type:
            temp_dic = dict()
            if type['StimType_Rank'] == '1':
                temp_dic['id'] = type['StimType_ID']
                temp_dic['title'] = type['StimType_Name']
                children = create_tree(stim_type, type['StimType_Name'], "措施")
                if children:
                    temp_dic['children'] = children
                stim_type_tree.append(temp_dic)
        return stim_type_tree
    except Exception as e:
        print(e)
        return False

def get_all_year():
    try:
        allyear=set()
        year=session_pub.query(md_pub.TbYearDvpunitOilstimPlanprojForecast.Year).all()
        if year:
            for data in year:
                allyear.add(data[0])
        year = session_pub.query(md_pub.TbYearDvpunitPlanprojOilstimCost.Year).all()
        if year:
            for data in year:
                allyear.add(data[0])
        year = session_pub.query(md_pub.TbYearDvpunitPlanprojOilprice.Year).all()
        if year:
            for data in year:
                allyear.add(data[0])
        year = session_pub.query(md_pub.TbYearDvpunitPlanprojOilprodcost.Year).all()
        if year:
            for data in year:
                allyear.add(data[0])
        year = session_pub.query(md_pub.TbYearDvpunitPlanprojOilsalecostRatio.Year).all()
        if year:
            for data in year:
                allyear.add(data[0])
        year = session_pub.query(md_pub.TbYearDvpunitPlanprojDiscount.Year).all()
        if year:
            for data in year:
                allyear.add(data[0])
        allyear=sorted(list(allyear))
        return allyear
    except Exception as e:
        print(e)

def plan_name_and_dvpname_and_stimtype():
    try:
        planname_and_dvpname = list()
        planname_and_stimtype = list()
        planname_and_year = list()
        plan_infs = session_pub.query(
            md_pub.TbYearDvpunitOilstimPlanprojForecast.DvpUnit_OilStim_PlanProj_Name,
            md_pub.TbYearDvpunitOilstimPlanprojForecast.DvpUnit_Name,
            md_pub.TbYearDvpunitOilstimPlanprojForecast.StimType_Name,
            md_pub.TbYearDvpunitOilstimPlanprojForecast.Year
        ).all()
        plan_infs=[dict(zip(plan.keys(), plan)) for plan in plan_infs]
        num=len(plan_infs)
        dvpname_count=0
        stimtype_count=0
        year_count=0
        if plan_infs:
            plan_name=set()
            plan_name_dict=dict()
            temp_num=0
            for plan in plan_infs:
                if plan['DvpUnit_OilStim_PlanProj_Name'] not in plan_name:
                    plan_name.add(plan['DvpUnit_OilStim_PlanProj_Name'])
                    planname_and_dvpname.append({
                        'id':dvpname_count,
                        'title':plan['DvpUnit_OilStim_PlanProj_Name'],
                        'child':[]
                    })
                    planname_and_stimtype.append({
                        'id': dvpname_count,
                        'title': plan['DvpUnit_OilStim_PlanProj_Name'],
                        'child': []
                    })
                    planname_and_year.append({
                        'id': dvpname_count,
                        'title': plan['DvpUnit_OilStim_PlanProj_Name'],
                        'child': []
                    })
                    dvpname_count+=1
                    stimtype_count+=1
                    year_count+=1
                    plan_name_dict.update({
                        f'{plan["DvpUnit_OilStim_PlanProj_Name"]}': temp_num
                    })
                    temp_num+=1
            for plan in plan_infs:
                plan_and_dvpname_inf=planname_and_dvpname[plan_name_dict.get(plan['DvpUnit_OilStim_PlanProj_Name'])]
                plan_and_type_inf=planname_and_stimtype[plan_name_dict.get(plan['DvpUnit_OilStim_PlanProj_Name'])]
                plan_and_year_inf=planname_and_year[plan_name_dict.get(plan['DvpUnit_OilStim_PlanProj_Name'])]
                plan_and_dvpname=set()
                plan_and_typename=set()
                plan_and_year=set()
                for inf in plan_and_dvpname_inf['child']:
                    plan_and_dvpname.add(inf['title'])
                if plan['DvpUnit_Name'] not in plan_and_dvpname:
                    plan_and_dvpname_inf['child'].append({
                        'id':dvpname_count,
                        'title':plan['DvpUnit_Name']
                    })
                    dvpname_count+=1

                for inf in plan_and_type_inf['child']:
                    plan_and_typename.add(inf['title'])
                if plan['StimType_Name'] not in plan_and_typename:
                    plan_and_type_inf['child'].append({
                        'id':stimtype_count,
                        'title':plan['StimType_Name']
                    })
                    stimtype_count+=1

                plan_and_year_inf['child'].append(plan['Year'])
            for inf in planname_and_year:
                inf['child']=sorted(inf['child'])
                start_year=inf['child'][0]
                end_year=inf['child'][len(inf['child'])-1]
                inf['child'].clear()
                inf['child'].append(start_year)
                inf['child'].append(end_year)
        return planname_and_dvpname,planname_and_stimtype,planname_and_year
    except Exception as e:
        print(e)
        return False

def get_all_projname():
    try:
        all_proj_name = session_pub.query(md_pub.TbYearDvpunitOilstimPlanprojMessage.DvpUnit_OilStim_PlanProj_Name).all()
        result = set()
        for data in all_proj_name:
            result.add(data[0])
        result = list(result)
        return result
    except Exception as e:
        print(e, e.__traceback__.tb_lineno)
        return False